﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using Microsoft.SharePoint;
using Kiiro.Common.Entities;
using Kiiro.Common.Utility;
using Kiiro.ExceptionManagement;

using WorkFieldNames = Kiiro.Common.Utility.Constants.WorkFieldNames;
using SPListNames = Kiiro.Common.Utility.Constants.SPListNames;

namespace Kiiro.DataAccess
{
    public class WorkDA
    {
        #region Singleton

        private WorkDA ( )
        {
        }

        private static WorkDA instance = null;
        private static readonly object threadLock = new object ( );

        public static WorkDA Instance
        {
            get
            {
                lock ( threadLock )
                {
                    if ( instance == null )
                    {
                        instance = new WorkDA ( );
                    }
                }
                return instance;
            }
        }

        #endregion

        /// <summary>
        /// Gets a list of Work entities. If workIds is null or empty this method returns ALL Work.
        /// </summary>
        /// <param name="workIds">The list of work ids to get. If workIds is null or empty this method returns ALL Work.</param>
        /// <param name="siteCollectionId">The site collection id</param>
        /// <param name="serverRelativeUrl">The server relative url</param>
        /// <returns>If workIds is null or empty this method returns ALL Work.</returns>
        public List<Work> GetWorkByIds ( List<int> workIds, Guid siteCollectionId, string serverRelativeUrl )
        {
            List<Work> work = new List<Work> ( );
            SPList list = null;

            using ( SPWeb web = SPHelpers.GetWeb ( siteCollectionId, serverRelativeUrl ) )
            {
                try
                {
                    list = web.Lists[Constants.SPListNames.Work];
                }
                catch (Exception ex)
                {
                    ExceptionManager.HandleException(ex, false);
                }

                if ( list != null )
                {
                    SPQuery query = new SPQuery ( );

                    bool first = true;
                    if ( workIds != null && workIds.Count > 0 )
                    {
                        foreach ( int workId in workIds )
                        {
                            if ( !first ) query.Query = "<Or>" + query.Query;
                            query.Query += "<Eq><FieldRef Name='ID'/><Value Type='Integer'>" + workId + "</Value></Eq>";
                            if ( !first ) query.Query += "</Or>";

                            first = false;
                        }
                    }

                    if ( !string.IsNullOrEmpty ( query.Query ) )
                        query.Query = "<Where>" + query.Query + "</Where>";

                    SPListItemCollection listItems = list.GetItems ( query );
                    if ( listItems != null )
                    {
                        foreach ( SPListItem listItem in listItems )
                        {
                            try
                            {
                                work.Add ( SPDataHelpers.MakeWork ( listItem ) );
                            }
                            catch ( Exception ex )
                            {
                                ExceptionManager.HandleException ( ex, false );
                            }
                        }
                    }
                }
            }
            return work;
        }

        public List<Work> GetAllTasks ( Guid siteCollectionId, string serverRelativeUrl )
        {
            List<Work> tasks = new List<Work> ( );

            try
            {
                using ( SPWeb web = SPHelpers.GetWeb ( siteCollectionId, serverRelativeUrl ) )
                {
                    StringBuilder queryText = new StringBuilder ( );
                    queryText.Append ( "<Where>" );
                    queryText.Append ( "  <And>" );
                    queryText.Append ( "    <IsNotNull>" );
                    queryText.Append ( "      <FieldRef Name='" + WorkFieldNames.ItemId + "'/>" );
                    queryText.Append ( "    </IsNotNull>" );
                    queryText.Append ( "    <Eq>" );
                    queryText.Append ( "      <FieldRef Name='" + WorkFieldNames.WorkItemType + "'/>" );
                    queryText.Append ( "      <Value Type='Choice'>" + WorkType.Task + "</Value>" );
                    queryText.Append ( "    </Eq>" );
                    queryText.Append ( "  </And>" );
                    queryText.Append ( "</Where>" );

                    SPQuery query = new SPQuery ( );
                    query.Query = queryText.ToString ( );

                    SPList list = web.Lists [ SPListNames.Work ];

                    if ( list != null )
                    {
                        DataTable dataTable = list.GetItems ( query ).GetDataTable ( );

                        if ( dataTable != null )
                        {
                            tasks = new List<Work> ( );
                            foreach ( DataRow dataRow in dataTable.Rows )
                            {
                                tasks.Add ( SPDataHelpers.MakeWork ( dataRow ) );
                            }
                        }
                    }
                }
            }
            catch ( Exception ex )
            {
                ExceptionManager.HandleException ( ex, true );
            }

            return tasks;
        }

        public List<Work> GetMyWork ( Guid siteCollectionId, string serverRelativeUrl, User currentUser )
        {
            List<Work> myWork = null;

            try
            {
                using ( SPWeb web = SPHelpers.GetWeb ( siteCollectionId, serverRelativeUrl ) )
                {
                    myWork = new List<Work> ( );

                    StringBuilder queryText = new StringBuilder ( );
                    queryText.Append ( "<Where>" );
                    queryText.Append ( "  <Contains>" );
                    queryText.Append ( "    <FieldRef Name='" + WorkFieldNames.AssignedToList + "'/>" );
                    queryText.Append ( "    <Value Type='Text'>;#" + currentUser.Id.ToString ( ) + ";#</Value>" );
                    queryText.Append ( "  </Contains>" );
                    queryText.Append ( "</Where>" );
                    queryText.Append ( "<OrderBy>" );
                    queryText.Append ( "  <FieldRef Name='" + WorkFieldNames.EndDate + "' Ascending='TRUE'/>" );
                    queryText.Append ( "  <FieldRef Name='" + WorkFieldNames.Title + "' Ascending='TRUE'/>" );
                    queryText.Append ( "</OrderBy>" );

                    SPQuery query = new SPQuery ( );
                    query.Query = queryText.ToString ( );

                    SPList list = web.GetList ( SPHelpers.GetServerRelativeUrl ( web ) + Constants.SPListUrls.Work );

                    if ( list != null )
                    {
                        SPListItemCollection listItems = list.GetItems ( query );

                        if ( listItems != null && listItems.Count > 0 )
                        {
                            for ( int itemsCount = 0; itemsCount < listItems.Count; itemsCount++ )
                            {
                                myWork.Add ( SPDataHelpers.MakeWork ( listItems [ itemsCount ] ) );
                            }
                        }
                    }
                }
            }
            catch ( Exception ex )
            {
                ExceptionManager.HandleException ( ex, true );
            }

            return myWork;
        }




        public Work Save ( Work work, Guid siteCollectionId, string serverRelativeUrl )
        {
            try
            {
                if ( work == null )
                    throw new Exception ( "Cannot SaveProject() when Project is null" );

                if ( work.EditType != EditType.Unchanged )
                {
                    using ( SPWeb web = SPHelpers.GetWeb ( siteCollectionId, serverRelativeUrl ) )
                    {
                        if ( work == null )
                            throw new Exception ( "Cannot SaveWork() when Work entity is null" );

                        if ( work.EditType != EditType.Unchanged )
                        {
                            SPList list = web.Lists [ SPListNames.Work ];

                            if ( list != null )
                            {
                                #region Get Item

                                SPListItem listItem = work.EditType == EditType.Created ? list.Folders.Add ( ) : list.Folders.GetItemById ( work.Id );

                                switch ( work.EditType )
                                {
                                    case EditType.Created:
                                        listItem [ WorkFieldNames.SiteCollectionId ] = list.ParentWeb.Site.ID;

                                        if ( work.CreatedBy != null && work.CreatedBy.Id > 0 )
                                            listItem [ WorkFieldNames.CreatedBy ] = work.CreatedBy.Id;
                                        if ( work.CreatedDate != null && work.CreatedDate != DateTime.MinValue )
                                            listItem [ WorkFieldNames.CreatedDate ] = work.CreatedDate;

                                        listItem [ WorkFieldNames.WorkItemType ] = work.WorkItemType;
                                        break;

                                    case EditType.Updated:
                                    case EditType.Deleted:
                                    default:
                                        break;
                                }
                                #endregion

                                #region Set Item Properties

                                if ( listItem != null && work.EditType != EditType.Deleted )
                                {


                                    if ( work.ModifiedBy != null && work.ModifiedBy.Id > 0 )
                                        listItem [ WorkFieldNames.ModifiedBy ] = work.ModifiedBy.Id;
                                    if ( work.ModifiedDate != null && work.ModifiedDate != DateTime.MinValue )
                                        listItem [ WorkFieldNames.ModifiedDate ] = work.ModifiedDate;

                                    if ( !string.IsNullOrEmpty ( work.Title ) )
                                        listItem [ WorkFieldNames.Title ] = work.Title;

                                    if ( !string.IsNullOrEmpty ( work.Description ) )
                                        listItem [ WorkFieldNames.Description ] = work.Description;

                                    if ( work.AssignedToList.Count >= 0 )
                                    {
                                        listItem [ WorkFieldNames.AssignedToList ] = FormattingHelpers.ListOfIntsToFieldText ( work.AssignedToList );
                                    }

                                    listItem [ WorkFieldNames.MembersEmail ] = work.MembersEmail;
                                    listItem [ WorkFieldNames.ProjectManagersEmail ] = work.ProjectManagersEmail;
                                    if ( !string.IsNullOrEmpty ( work.ParentTasks ) )
                                        listItem [ WorkFieldNames.ParentTasks ] = work.ParentTasks;
                                    else
                                        listItem [ WorkFieldNames.ParentTasks ] = string.Empty;


                                    if ( work.StartDate != null && work.StartDate != DateTime.MinValue )
                                        listItem [ WorkFieldNames.StartDate ] = work.StartDate;
                                    else
                                        listItem [ WorkFieldNames.StartDate ] = null;

                                    if ( work.EndDate != null && work.EndDate != DateTime.MinValue )
                                        listItem [ WorkFieldNames.EndDate ] = work.EndDate;
                                    else
                                        listItem [ WorkFieldNames.EndDate ] = null;


                                    if ( work.AssignedWork >= 0 )
                                        listItem [ WorkFieldNames.AssignedWork ] = work.AssignedWork;
                                    if ( work.PercentageComplete >= 0 )
                                        listItem [ WorkFieldNames.PercentageComplete ] = work.PercentageComplete;

                                    listItem [ WorkFieldNames.StatusNote ] = work.StatusNote;
                                    listItem [ WorkFieldNames.Status ] = work.Status;

                                    listItem [ WorkFieldNames.IsDirty ] = work.IsDirty;
                                    listItem [ WorkFieldNames.IsFromProjectPlan ] = work.IsFromProjectPlan;

                                    if ( work.PlanId > 0 )
                                        listItem [ WorkFieldNames.PlanId ] = work.PlanId;

                                    // TODO: Move business logic for calculating stats to the Model
                                    #region Task Stats

                                    if ( work.WorkItemType == WorkType.Task )
                                    {

                                        #region Is Late Calc

                                        bool isLateOrig = false;
                                        if ( listItem [ WorkFieldNames.IsLate ] != null && !string.IsNullOrEmpty ( listItem [ WorkFieldNames.IsLate ].ToString ( ) ) )
                                            isLateOrig = Boolean.Parse ( listItem [ WorkFieldNames.IsLate ].ToString ( ) );

                                        bool isLateNew = work.EndDate != DateTime.MinValue ? DateTimeHelpers.IsDateLaterThanToday ( work.EndDate ) && work.Status != StatusType.Completed : false;

                                        // remove from late stats
                                        work.IsLateDel = isLateOrig && ( !isLateNew || work.EditType == EditType.Deleted || ( work.Status == StatusType.Completed && work.EditType != EditType.Created ) );

                                        // add to late stats
                                        work.IsLateAdd = ( !isLateOrig && isLateNew ) || ( work.EditType == EditType.Created && isLateNew );

                                        #endregion

                                        #region Is Slipping Calc

                                        bool isSlippingOrig = false;
                                        if ( listItem [ WorkFieldNames.IsSlipping ] != null && !string.IsNullOrEmpty ( listItem [ WorkFieldNames.IsSlipping ].ToString ( ) ) )
                                            isSlippingOrig = Boolean.Parse ( listItem [ WorkFieldNames.IsSlipping ].ToString ( ) );

                                        double projectedActual = GetYesterdaysProjectedActual ( work );
                                        double actualWork = ( work.PercentageComplete * work.AssignedWork ) / 100;

                                        bool isSlippingNew = actualWork < projectedActual && work.EndDate.Date > DateTime.Now.Date;

                                        // remove from slipping stats
                                        work.IsSlippingDel = isSlippingOrig && ( ( !isSlippingNew || work.EditType == EditType.Deleted || work.IsLateAdd )
                                            || ( work.Status == StatusType.Completed && work.EditType != EditType.Created ) );

                                        // add to slipping stats
                                        work.IsSlippingAdd = ( !isSlippingOrig && isSlippingNew ) || ( work.EditType == EditType.Created && isSlippingNew );

                                        #endregion

                                        // set listItem values
                                        listItem [ WorkFieldNames.IsLate ] = isLateNew;
                                        listItem [ WorkFieldNames.IsSlipping ] = isSlippingNew;
                                        listItem [ WorkFieldNames.IsHighPriority ] = work.IsHighPriority;
                                    }

                                    #endregion

                                    // TODO: Move business logic for calculating stats to the Model
                                    #region Issue Stats

                                    if ( work.WorkItemType == WorkType.Issue )
                                    {

                                        #region Is Late Calc

                                        bool isLateOrig = false;
                                        if ( listItem [ WorkFieldNames.IsLate ] != null && !string.IsNullOrEmpty ( listItem [ WorkFieldNames.IsLate ].ToString ( ) ) )
                                            isLateOrig = Boolean.Parse ( listItem [ WorkFieldNames.IsLate ].ToString ( ) );

                                        bool isLateNew = DateTimeHelpers.IsDateLaterThanToday ( work.EndDate ) && work.Status != StatusType.Closed;

                                        // remove from late stats
                                        work.IsLateDel = ( isLateOrig && !isLateNew ) || work.EditType == EditType.Deleted || ( work.Status == StatusType.Completed && work.EditType != EditType.Created );

                                        // add to late stats
                                        work.IsLateAdd = ( !isLateOrig && isLateNew ) || ( work.EditType == EditType.Created && isLateNew );

                                        // set listItem value
                                        listItem [ WorkFieldNames.IsLate ] = isLateNew;

                                        #endregion

                                        #region Is High Priority Calc

                                        bool isHigPriOrig = false;
                                        if ( listItem [ WorkFieldNames.IsHighPriority ] != null && !string.IsNullOrEmpty ( listItem [ WorkFieldNames.IsHighPriority ].ToString ( ) ) )
                                            isHigPriOrig = Boolean.Parse ( listItem [ WorkFieldNames.IsHighPriority ].ToString ( ) );

                                        bool isHigPriNew = work.IsHighPriority && work.Status != StatusType.Closed;

                                        // remove from high priority stats
                                        work.IsHighPriorityDel = ( isHigPriOrig && !isHigPriNew ) || work.EditType == EditType.Deleted || ( work.Status == StatusType.Completed && work.EditType != EditType.Created );

                                        // add to high priority stats
                                        work.IsHighPriorityAdd = ( !isHigPriOrig && isHigPriNew ) || ( work.EditType == EditType.Created && isHigPriNew );

                                        #endregion

                                        // set listItem values
                                        listItem [ WorkFieldNames.IsLate ] = isLateNew;
                                        listItem [ WorkFieldNames.IsSlipping ] = work.IsSlipping;
                                        listItem [ WorkFieldNames.IsHighPriority ] = work.IsHighPriority;//isHigPriNew;
                                    }

                                    #endregion

                                    SPDataHelpers.PopulateCustomFieldsInListItemFromEntity ( listItem, work );
                                }

                                #endregion

                                #region Update / Delete Item

                                SPWeb parentWeb = null;

                                try
                                {
                                    parentWeb = list.ParentWeb;

                                    parentWeb.AllowUnsafeUpdates = true;
                                    if ( work.EditType == EditType.Deleted )
                                    {
                                        list.Folders.DeleteItemById ( listItem.ID );
                                    }
                                    else
                                    {
                                        listItem.Update ( );

                                        // TODO: Move business logic for calculating stats to the Model
                                        // do not override, just return id --> used to update project stats
                                        // work = SPDataHelpers.MakeWork(listItem);
                                        work.Id = listItem.ID;
                                    }
                                }
                                catch ( Exception ex )
                                {
                                    ExceptionManager.HandleException ( ex, true );
                                }
                                finally
                                {
                                    if ( parentWeb != null ) parentWeb.AllowUnsafeUpdates = false;
                                    if ( parentWeb != null ) parentWeb.Dispose ( );
                                }
                                #endregion
                            }
                        }
                    }
                }
            }
            catch ( Exception ex )
            {
                Logging.LoggingManager.LogMessage("EXCEPTION IDENTIFIER", string.Format("Unable to Update {0}. ID:{1}, Title:{2}, URL:{3}.", work.WorkItemType, work.Id, work.Title, work.ServerRelativeUrl));
                ExceptionManager.HandleException ( ex, true );
            }

            return work;
        }

        /// <summary>
        /// Gets a double value indicating the projectedActual value for the supplied work yesterday
        /// </summary>
        /// <param name="work"></param>
        /// <returns></returns>
        private double GetYesterdaysProjectedActual ( Work work )
        {
            int daysToComplete = DateTimeHelpers.GetNumberOfWorkDays ( work.StartDate, work.EndDate );
            int daysElapsed = DateTimeHelpers.GetNumberOfWorkDays ( work.StartDate, DateTime.Today.AddDays ( -1 ) );

            double projectedActual = ( work.AssignedWork / daysToComplete ) * daysElapsed;

            return projectedActual <= work.AssignedWork ? projectedActual : work.AssignedWork;
        }
        
        public List<Work> GetCurrentUserWorkOnProjects(Guid siteCollectionId, string serverRelativeUrl, User currentUser)
        {
            SPSiteDataQuery siteDataQuery = new SPSiteDataQuery();
            SPList list = null;
            List<Work> currentUserWork = new List<Work>();

            
            siteDataQuery.Lists = "<Lists><List Title='Kiiro Work List'/></Lists>";
            siteDataQuery.Query = "<Where>"+
                                        "<Contains>"+
                                            "<FieldRef Name='Work.AssignedToList'/>"+
                                            "<Value Type='Text'>;#" + currentUser.Id.ToString() + ";#</Value>" +
                                        "</Contains>"+
                                    "</Where>";
            siteDataQuery.Webs = "<Webs Scope='Recursive'/>";
            siteDataQuery.ViewFields =  "<FieldRef Name='ItemBase.Title'/><FieldRef Name='ID'/><FieldRef Name='ItemBase.SiteCollectionId'/>"+
                                        "<FieldRef Name='ItemBase.ServerRelativeUrl'/><FieldRef Name='ItemBase.CreatedBy'/>" +
                                        "<FieldRef Name='ItemBase.CreatedDate'/><FieldRef Name='ItemBase.ModifiedBy'/>" +
                                        "<FieldRef Name='ItemBase.ModifiedDate'/><FieldRef Name='ItemBase.IsNew'/><FieldRef Name='ItemBase.Description'/>" +
                                        "<FieldRef Name='Work.StartDate'/><FieldRef Name='Work.EndDate'/><FieldRef Name='Work.AssignedWork'/>" +
                                        "<FieldRef Name='Work.PercentageComplete'/><FieldRef Name='Work.StatusNote'/><FieldRef Name='Work.AssignedToList'/>" +
                                        "<FieldRef Name='Work.ParentTasks'/><FieldRef Name='Work.IsHighPriority'/><FieldRef Name='Work.WorkItemType'/>" +
                                        "<FieldRef Name='Work.Status'/><FieldRef Name='Work.IsLate'/><FieldRef Name='Work.IsSlipping'/>" +
                                        "<FieldRef Name='Work.IsFromProjectPlan'/><FieldRef Name='Work.IsDirty'/><FieldRef Name='Work.PlanId'/>" +
                                        "<FieldRef Name='Work.MembersEmail'/><FieldRef Name='Work.ProjectManagersEmail'/>";
            DataTable dataTable = null;
            using (SPWeb web = SPHelpers.GetPortalWeb(siteCollectionId, serverRelativeUrl))
            {
                dataTable = web.GetSiteData(siteDataQuery);
            }
            foreach (DataRow dataRow in dataTable.Rows)
            {
                currentUserWork.Add(SPDataHelpers.MakeWork(dataRow));
            }

            return currentUserWork;
        }
    }
}

/*************************************************************************
 * 
 * $Log: /Internal Projects/Kiiro/Kiiro.DataAccess/WorkDA.cs $
 * 
 * 20    2/08/10 5:04p Camerons
 * 
 * 19    1/25/10 1:17p Camerons
 * 
 * 18    1/12/10 12:02p Camerons
 * 
 * 17    26/11/09 10:35a Camerons
 * 
 * 16    17/11/09 6:46p Camerons
 * 
 * 15    26/10/09 4:37p Camerons
 * Fogbugz case #287 - Add trailing ;# to SPQuery in GetMyWork( )
 * 
 * 14    10/09/09 10:30a Stefanf
 * 
 * 13    22/09/09 6:03p Camerons
 * Code changes to support creating user on demand - version 1.1.x.x
 * 
 * 12    10/09/09 9:23a Shafia
 * 
 * 11    17/08/09 10:41a Camerons
 * Added check-in comments section
 * 
 ************************************************************************/